<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class admin_dashboard_model extends CI_Model
{
	
	public function __construct()
	{
		parent::__construct();

	
	} 
	
public function get_image($id)
{

$query = $this->db->get_where('user_image', array('user_id' => $id) );
return $query;


}	
public function update_image($id,$image)
{
$insert=array('user_id'=>$id,
               'name' =>$image,);
			 	$this->db->update('user_image',$insert,array('user_id'=>$id));
	

}
public function insert_image($id)
{
$data=$this->db->get_where('user_image', array('user_id' =>$id))->num_rows();
if($data==0)
{
$insert= array('user_id'=>$id,);
$this->db->insert('user_image',$insert);

}	
else{ }
}	
public function company_profile()
{
return $this->db->get('company_profile');

}
	
	
public function get_month_caseid()
{
$sql = "SELECT case.id\n"
    . "FROM `case` \n"
    . "WHERE YEAR( DATE ) = YEAR( NOW( ) ) \n"
    . "AND MONTH( DATE ) = MONTH( NOW( ) ) \n"
    . "LIMIT 0 , 30";
$data=count($this->db->query( $sql)->result());
return $data;
}
public function get_nonwarranty_case()
{
$sql1 = "SELECT nonwarranty_case.id\n"
    . "FROM `nonwarranty_case` \n"
    . "WHERE YEAR( DATE ) = YEAR( NOW( ) ) \n"
    . "AND MONTH( DATE ) = MONTH( NOW( ) ) \n"
    . "LIMIT 0 , 30";
$data1=count($this->db->query( $sql1)->result());
return $data1;
}	
public function get_detail()
{ 
$sql = "SELECT case.id, case.case_no, case.status_id,users.username\n"
    . "FROM `case` \n"
    . "JOIN users ON case.engineer_id=users.id\n"
    . "WHERE YEAR( DATE ) = YEAR( NOW( ) ) \n"
    . "AND MONTH( DATE ) = MONTH( NOW( ) ) \n"
	  . "AND DATE( DATE ) = DATE( NOW( ) ) \n"
    . "LIMIT 0 , 30\n"
    . "";
  $data=$this->db->query($sql)->result();
	return $data;  
	

}	
public function get_nonwarrantydetail()
{ 
$sql = "SELECT nonwarranty_case.id, nonwarranty_case.case_no, nonwarranty_case.status_id,users.username\n"
    . "FROM `nonwarranty_case` \n"
    . "JOIN users ON nonwarranty_case.engineer_id=users.id\n"
    . "WHERE YEAR( DATE ) = YEAR( NOW( ) ) \n"
    . "AND MONTH( DATE ) = MONTH( NOW( ) ) \n"
	  . "AND DATE( DATE ) = DATE( NOW( ) ) \n"
    . "LIMIT 0 , 30\n"
    . "";
  $data=$this->db->query($sql)->result();
	return $data;  
	

}	
public function get_engineer($id)
{
$this->db->select('users.username');
	$this->db->from('diagnosis', 'case','users' );
	$this->db->join('case', 'diagnosis.service_id= case.service_id  '  );
	$this->db->join('users','diagnosis.enginer_id=users.id');
	$this->db->where('case.id',$id);
	$q = $this->db->get();
	if($q!=="")
	return $q;
	else
  $this->db->select('users.username');
	$this->db->from('repair', 'case','users' );
	$this->db->join('case', 'repair.case_id= case.id  '  );
	$this->db->join('users','repair.engineer_id=users.id');
	$this->db->where('case.id',$id);
	$q = $this->db->get();
  return $q;		


}	
	
public function get_month_diagnosis()
{
$sql = "SELECT diagnosis.id\n"
    . "FROM `diagnosis` \n"
	 . "WHERE YEAR( DATE ) = YEAR( NOW( ) ) \n"
    . "AND MONTH( DATE ) = MONTH( NOW( ) ) \n"
    . "LIMIT 0 , 30";
$data=count($this->db->query( $sql)->result());
$sql1 = "SELECT nonwarranty_diagnosis.id\n"
    . "FROM `nonwarranty_diagnosis` \n"
	 . "WHERE YEAR( DATE ) = YEAR( NOW( ) ) \n"
    . "AND MONTH( DATE ) = MONTH( NOW( ) ) \n"
    . "LIMIT 0 , 30";
$data1=count($this->db->query( $sql1)->result());
return $data+$data1;


}	
public function get_month_repair()
{
$sql = "SELECT repair.id\n"
    . "FROM `repair` \n"
    . "WHERE YEAR( ASSIGNED_DATE) = YEAR( NOW( ) ) \n"
    . "AND MONTH( ASSIGNED_DATE ) = MONTH( NOW( ) ) \n"
    . "LIMIT 0 , 30";
$data=count($this->db->query( $sql)->result());
$sql1 = "SELECT nonwarranty_repair.id\n"
    . "FROM `nonwarranty_repair` \n"
    . "WHERE YEAR( ASSIGNED_DATE) = YEAR( NOW( ) ) \n"
    . "AND MONTH( ASSIGNED_DATE ) = MONTH( NOW( ) ) \n"
    . "LIMIT 0 , 30";
$data1=count($this->db->query( $sql1)->result());
return $data+$data1;


}		
public function get_engineers()
	{
	$this->db->select('users.id');
	$this->db->from('users', 'users_groups' );
	$this->db->join('users_groups', 'users.id= users_groups.user_id   '  );
	$this->db->where('users_groups.group_id',2);
	$q = $this->db->get()->result();
		
			return count($q);
			}			
public function get_active_engineer()
	{
	$this->db->select('login_status.id');
	$this->db->from('login_status' );
	$this->db->where('login_status.status',1);
	$q = $this->db->get()->result();
		
			return count($q);
			}			
		
public function update_profile($insert,$id)
{
$this->db->update('users',$insert,array('id'=>$id));

}	
public function get_diagnosis()
{
$data=count($this->db->get_where('diagnosis', array('status_id'=>3,))->result());
$data1=count($this->db->get_where('nonwarranty_diagnosis', array('status_id'=>3,))->result());
echo $data+$data1;
}
public function get_repair()
{
$data=count($this->db->get_where('repair', array('status_id'=>10,))->result());
$data1=count($this->db->get_where('nonwarranty_repair', array('status_id'=>11,))->result());
echo $data+$data1;
}		
	
public function update_company($insert,$id)
{
$this->db->update('company_profile',$insert,array('id'=>$id));

}	
public function get_profile($id)
{
$data=$this->db->get_where('users', array('id' =>$id));
return $data;

}	
	
	
	
}
?>